package financial.mock;

import net.datafaker.Faker;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.ThreadLocalRandom;

public class MysqlDataInserterStreaming {
    private static final Faker faker = new Faker(new Locale("zh-CN"));
    private static final Random random = new Random(System.currentTimeMillis());
    private static final DateTimeFormatter DATE_FORMAT = DateTimeFormatter.ofPattern("yyyy-MM-dd");
    private static final DateTimeFormatter TIMESTAMP_FORMAT = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
    private static final String DT = new SimpleDateFormat("yyyyMMdd").format(new Date(System.currentTimeMillis()));
    private static final int MINI_BATCH_RECORDS = 10;
    private static final int MINI_BATCH_GAP_MILLS = 100;
    private static final int TOTAL_BATCHS   = 1000;


    // MySQL配置
    private static final String JDBC_URL = Config.MYSQL_URL;
    private static final String USER = Config.MYSQL_USER;
    private static final String PASSWORD = Config.MYSQL_PASSWORD;

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(JDBC_URL, USER, PASSWORD)) {
            // 1. 插入交易参考数据
            // insertReferenceData(conn);

            for (int i = 0; i < TOTAL_BATCHS; i++) {


                // 2. 生成并插入客户数据
                List<String> customerIds = insertCustomerData(conn);

                // 3. 生成并插入账户数据
                List<String> accountNos = insertAccountData(conn, customerIds);

                // 4. 生成并插入交易数据
                insertTransactionData(conn, accountNos);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 插入交易参考表（静态数据）
    private static void insertReferenceData(Connection conn) throws SQLException {
        String sql = "INSERT INTO fin_transaction_reference (transaction_code, description, category, dt) VALUES (?,?,?,?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            String[][] data = {
                    {"T001", "转账", "支出", DT},
                    {"T002", "消费", "支出", DT},
                    {"T003", "存款", "收入", DT}
            };
            for (String[] row : data) {
                pstmt.setString(1, row[0]);
                pstmt.setString(2, row[1]);
                pstmt.setString(3, row[2]);
                pstmt.setString(4, row[3]);
                pstmt.addBatch();
            }
            pstmt.executeBatch();

        }
    }

    // 插入客户信息表
    private static List<String> insertCustomerData(Connection conn) throws SQLException {
        List<String> customerIds = new ArrayList<>(MINI_BATCH_RECORDS);
        String sql = "INSERT INTO fin_customer_info (customer_id, name, id_card_no, phone, address, create_time, update_time, dt) " +
                "VALUES (?,?,?,?,?,?,?,?)";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            for (int i = 0; i < MINI_BATCH_RECORDS; i++) {
                String customerId = UUID.randomUUID().toString();
                customerIds.add(customerId);

                pstmt.setString(1, customerId);
                pstmt.setString(2, faker.name().fullName());
                pstmt.setString(3, generateValidIdCard());
                pstmt.setString(4, faker.phoneNumber().cellPhone());
                pstmt.setString(5, faker.address().fullAddress());
                pstmt.setString(6, generatePastTime(365));
                pstmt.setString(7, generateRecentTime(30));
                pstmt.setString(8, DT);
                pstmt.addBatch();
                try {
                    pstmt.execute();
                    Thread.sleep(MINI_BATCH_GAP_MILLS);

                } catch (Exception e) {
                    e.printStackTrace();
                }

            }


        }
        return customerIds;
    }

    // 插入账户信息表
    private static List<String> insertAccountData(Connection conn, List<String> customerIds) throws SQLException {
        List<String> accountNos = new ArrayList<>(MINI_BATCH_RECORDS);
        String sql = "INSERT INTO fin_account_info (account_no, customer_id, account_type, balance, open_date, status, last_updated_time, dt) " +
                "VALUES (?,?,?,?,?,?,?,?)";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            for (int i = 0; i < MINI_BATCH_RECORDS; i++) {
                String accountNo = "ACCT" + System.nanoTime() + ThreadLocalRandom.current().nextInt(1000, 9999);
                accountNos.add(accountNo);

                pstmt.setString(1, accountNo);
                pstmt.setString(2, customerIds.get(i));
                pstmt.setString(3, faker.options().option("储蓄账户", "对公账户"));
                pstmt.setString(4, String.format("%.2f", faker.number().randomDouble(2, 1000, 1000000)));
                pstmt.setString(5, generatePastDate(365 * 5));
                pstmt.setString(6, faker.options().option("正常", "冻结"));
                pstmt.setString(7, generateRecentTime(30));
                pstmt.setString(8, DT);
                pstmt.addBatch();


                try {
                    pstmt.execute();
                    Thread.sleep(MINI_BATCH_GAP_MILLS);
                } catch (Exception e) {
                    e.printStackTrace();
                }

            }

        }
        return accountNos;
    }

    // 插入交易流水表
    private static void insertTransactionData(Connection conn, List<String> accountNos) throws SQLException {

        String[] transCodes = {"T001", "T002", "T003"};
        String sql = "INSERT INTO fin_transaction_flow (transaction_id, account_no, transaction_code, amount, currency, " +
                "counterparty_account, transaction_time, status, channel, dt) " +
                "VALUES (?,?,?,?,?,?,?,?,?,?)";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            for (int i = 0; i < MINI_BATCH_RECORDS * 20; i++) {
                String txId = "TX" + System.currentTimeMillis() + UUID.randomUUID();


                pstmt.setString(1, txId);
                pstmt.setString(2, accountNos.get(random.nextInt(accountNos.size())));
                pstmt.setString(3, transCodes[i % transCodes.length]);
                pstmt.setString(4, String.format("%.2f", faker.number().randomDouble(2, 10, 50000)));
                pstmt.setString(5, faker.money().currencyCode());
                pstmt.setString(6, getRandomAccount(accountNos));
                pstmt.setString(7, generatePastTime(30));
                pstmt.setString(8, faker.options().option("成功", "失败"));
                pstmt.setString(9, faker.options().option("ATM", "网银", "手机银行"));
                pstmt.setString(10, DT);
                pstmt.addBatch();

                try {
                    pstmt.execute();
                    Thread.sleep(MINI_BATCH_GAP_MILLS);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }

    }


    // 生成有效身份证号
    private static String generateValidIdCard() {
        return faker.idNumber().ssnValid();
    }

    // 生成过去时间的字符串（格式：yyyy-MM-dd HH:mm:ss）
    private static String generatePastTime(int maxDays) {
        return TIMESTAMP_FORMAT.format(
                LocalDateTime.now().minusDays(ThreadLocalRandom.current().nextInt(1, maxDays + 1))
        );
    }

    // 生成过去日期（格式：yyyy-MM-dd）
    private static String generatePastDate(int maxDays) {
        return DATE_FORMAT.format(
                LocalDateTime.now().minusDays(ThreadLocalRandom.current().nextInt(1, maxDays + 1))
        );
    }

    // 生成最近时间的字符串
    private static String generateRecentTime(int maxDays) {
        return TIMESTAMP_FORMAT.format(
                LocalDateTime.now().minusHours(ThreadLocalRandom.current().nextInt(1, maxDays * 24))
        );
    }

    // 随机获取账户号
    private static String getRandomAccount(List<String> accounts) {
        return accounts.get(ThreadLocalRandom.current().nextInt(accounts.size()));
    }
}